package db.commodity;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import db.Database;
import dbservice.commodity.CommodityDBService;
import po.AccountBookPo;
import po.CommodityPo;
import po.Po;

public class CommodityDB implements CommodityDBService{
	
	/*public static void main(String[] args){
		try {
			Database.iniDatabase("127.0.0.1",3306,"seac2?useUnicode=true&characterEncoding=utf-8","developer", "19960623");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		AccountBookPo a=new AccountBookPo();
		a.setName("accountbook");
		CommodityPo po=new CommodityPo();
		po.set名称("旺旺碎碎冰");
		po.set进价(10);
		po.set售价(20000);
		po.set型号("rsxt");
		po.set库存数量(300);
		po.set库存数量警戒值(10);
		po.set所属分类("旺旺食品");
		try {
			//CommodityDB db=new CommodityDB(a);
		/*	List<CommodityPo> list=db.find(po);
			for(int i=0;i<list.size();i++){
				CommodityPo cm=list.get(i);
				System.out.println(cm.get编号()+" "+cm.get名称()+" "+cm.get型号()+" "+cm.get所属分类()+" "+cm.get售价()+" "+cm.get进价()+" "+cm.get最近售价()+" "+cm.get最近进价()+" "+cm.get库存数量()+" "+cm.get库存数量警戒值());
			}*/
			//System.out.println(db.get(po).get编号()+" "+db.get(po).get售价()+" "+db.get(po).get型号());
			//Po r=db.add(po);
			/*Po r=db.update(po);
			if(r.isSuccessed()){
				System.out.println("添加成功！");
			}*/
		/*} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}*/
	
	AccountBookPo accountBookPo;
	public CommodityDB(AccountBookPo accountBookPo) throws SQLException {
	    String createT_Commodity="CREATE TABLE IF NOT EXISTS `seac2`.`"+accountBookPo.getName()+ "_commodity` ( `f_commodityID` varchar(20) NOT NULL, `f_commodityTypeID` varchar(128) NOT NULL, `f_name` varchar(20) NOT NULL, `f_model` varchar(20) NOT NULL, `f_stockNum` int(8) NOT NULL,`f_totalMoney` bigint(0) NOT NULL,`f_stockAveragePrice` bigint(0) NOT NULL,`f_importPrice` bigint(0) NOT NULL, `f_salePrice` bigint(0) NOT NULL, `f_recent_importPrice` bigint(0) NOT NULL, `f_recent_salePrice` bigint(0) NOT NULL, `f_dangerNum` int(11) NOT NULL,PRIMARY KEY (`f_commodityID`), UNIQUE KEY `f_commodityID` (`f_commodityID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
	    Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(createT_Commodity);
		s.close();
		this.accountBookPo=accountBookPo;
	}
	
	public static String setCommodityId(String prefix,int num) {
		num++;
	    NumberFormat nf = new DecimalFormat("00000");
	    String _num = nf.format(num);
        return prefix+"-"+_num;
    }

	/**
	 * 得到与参数同名称的商品
	 * @return
	 * 返回该商品的详细信息
	 * 
	 */
/*	public List<CommodityPo> getCommodityItem(CommodityPo commodity){                  //根据商品分类得到商品列表
		List<CommodityPo> list=new ArrayList<CommodityPo>();
		String typeID=commodity.get所属分类();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement(); 
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodity where f_commodityTypeID='"+typeID+"'");
			while(rs.next()){
				CommodityPo cm=new CommodityPo();
				String name=rs.getString("f_name");
				String commodityTypeID=rs.getString("f_commodityTypeID");
				String commodityTypeName=getCommodityTypeName(commodityTypeID);
				String id=rs.getString("f_commodityID");
				String model=rs.getString("f_model");
				int stockNum=rs.getInt("f_stockNum");
				long importPrice=rs.getLong("f_importPrice");
				long salePrice=rs.getLong("f_salePrice");
				long recent_importPrice=rs.getLong("f_recent_importPrice");
				long recent_salePrice=rs.getLong("f_recent_salePrice");
				int dangerNum=rs.getInt("f_dangerNum");
			    cm.set名称(name);
			    cm.set售价(salePrice);
			    cm.set型号(model);
			    cm.set库存数量(stockNum);
			    cm.set库存数量警戒值(dangerNum);
			    cm.set所属分类(commodityTypeName);
			    cm.set最近售价(recent_salePrice);
			    cm.set最近进价(recent_importPrice);
			    cm.set编号(id);
			    cm.set进价(importPrice);
			    list.add(cm);
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		return list;
		
	}
	*/
	
/*	public CommodityPo get(CommodityPo commodity){          //根据商品名称得到商品信息
		String name =commodity.get名称();
		CommodityPo cm=new CommodityPo();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();ResultSet rs = null;
			 rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodity where f_name='"+name+"'");
			while(rs.next()){
				//name=rs.getString("f_name");
				String commodityTypeID=rs.getString("f_commodityTypeID");
				String commodityTypeName=getCommodityTypeName(commodityTypeID);
				String id=rs.getString("f_commodityID");
				String model=rs.getString("f_model");
				int stockNum=rs.getInt("f_stockNum");
				long importPrice=rs.getLong("f_importPrice");
				long salePrice=rs.getLong("f_salePrice");
				long recent_importPrice=rs.getLong("f_recent_importPrice");
				long recent_salePrice=rs.getLong("f_recent_salePrice");
				int dangerNum=rs.getInt("f_dangerNum");
			    cm.set名称(name);
			    cm.set售价(salePrice);
			    cm.set型号(model);
			    cm.set库存数量(stockNum);
			    cm.set库存数量警戒值(dangerNum);
			    cm.set所属分类(commodityTypeName);
			    cm.set最近售价(recent_salePrice);
			    cm.set最近进价(recent_importPrice);
			    cm.set编号(id);
			    cm.set进价(importPrice);
			}
			
		} catch (SQLException e) {
			cm.failed(e.getMessage());
			e.printStackTrace();
		}
		return cm;
		
	}
	*/
	/**
	 * 得到所有商品
	 * 
	 * @return
	 * 返回商品列表中的所有商品
	 * 
	 */
	public List<CommodityPo> getAll(){
		List<CommodityPo> list=new ArrayList<CommodityPo>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodity");
			while(rs.next()){
				CommodityPo cm=new CommodityPo();
				String name=rs.getString("f_name");
				String commodityTypeID=rs.getString("f_commodityTypeID");
				String commodityTypeName=getCommodityTypeName(commodityTypeID);
				String id=rs.getString("f_commodityID");
				String model=rs.getString("f_model");
				int stockNum=rs.getInt("f_stockNum");
				long importPrice=rs.getLong("f_importPrice");
				long salePrice=rs.getLong("f_salePrice");
				long recent_importPrice=rs.getLong("f_recent_importPrice");
				long recent_salePrice=rs.getLong("f_recent_salePrice");
				int dangerNum=rs.getInt("f_dangerNum");
				long average=rs.getLong("f_stockAveragePrice");
			    cm.set名称(name);
			    cm.set售价(salePrice);
			    cm.set型号(model);
			    cm.set库存数量(stockNum);
			    cm.set库存数量警戒值(dangerNum);
			    cm.set所属分类(commodityTypeName);
			    cm.set最近售价(recent_salePrice);
			    cm.set最近进价(recent_importPrice);
			    cm.set编号(id);
			    cm.set进价(importPrice);
			    cm.set库存均价(average);
			    list.add(cm);
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		return list;
		
	}
	
	/**
	 * 向商品列表添加商品
	 * @param commodity
	 * 需要添加的商品的详细信息
	 * @return
	 * 是否添加成功
	 */
	public Po add(CommodityPo commodity){
		//String id=commodity.get编号();          //商品编号自动生成
		String commodityTypeName=commodity.get所属分类();
		String commodityTypeID = getCommodityTypeID(commodityTypeName);
		String name=commodity.get名称();
		String model=commodity.get型号();         //商品型号
		int stockNum=commodity.get库存数量();       //0
		long totalMoney=commodity.get库存总价();    //0
		long stockAveragePrice=commodity.get库存均价();   //0
		long importPrice=commodity.get进价();
		long salePrice=commodity.get售价();
		//long recent_importPrice=commodity.get最近进价(); //由进货单得到,默认为进价
		//long recent_salePrice=commodity.get最近售价();     //由销售单决定，默认为售价
		int dangerNum=commodity.get库存数量警戒值();
		
		Po m=new Po(); String prefix="cm";
		Connection c=Database.getConnection();
		Statement s;
		try {
			int total=0;
			s = c.createStatement();
			ResultSet rs1=s.executeQuery("select max(f_commodityID) from "+accountBookPo.getName()+"_commodity");
			while(rs1.next()){
				if(rs1.getString(1)!=null)
			          total=Integer.parseInt(rs1.getString(1).substring(prefix.length()+1));
			}
			if(commodityTypeID!=""){
			s.execute("insert into "+accountBookPo.getName()+"_commodity values('"+setCommodityId(prefix,total)+"','"+commodityTypeID+"','"+name+"','"+model+"','"+stockNum+"','"+totalMoney+"','"+stockAveragePrice+"','"+importPrice+"','"+salePrice+"','"+importPrice+"','"+salePrice+"','"+dangerNum+"')");
			//将相应的commodityTYPE设为hasCommodity
			s.execute("update "+accountBookPo.getName()+"_commodityType set f_hasCommodity='1' where f_commodityTypeID='"+commodityTypeID+"'");
			m.successed();
			}else{
				m.failed("您填写的商品分类不存在！");
				return m;
			}
			
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
	

	/**
	 * 删除含有相同关键字的商品
	 * @param keyword
	 */
	public Po delete(CommodityPo commodity){
		String id=commodity.get编号();
		String commodityTypeID="";
		Po m=new Po(); 
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select f_commodityTypeID from "+accountBookPo.getName()+"_commodity where f_commodityID='"+id+"'");
			while(rs.next()){
				commodityTypeID=rs.getString(1);
			}
			s.executeUpdate("delete from "+accountBookPo.getName()+"_commodity where f_commodityID='"+id+"'");
			ResultSet rs1=s.executeQuery("select * from "+accountBookPo.getName()+"_commodity where f_commodityTypeID='"+commodityTypeID+"'");
			if(!rs1.next()){
				s.execute("update "+accountBookPo.getName()+"_commodityType set f_hasCommodity='"+0+"' where f_commodityTypeID='"+commodityTypeID+"'");
			}
			m.successed();
			
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	/**
	 * 更新同名称的商品信息
	 * @param commodity
	 * 新的商品信息（编号、库存数量、最近进价、最近售价不能更改）
	 * @return	
	 * 是否更新成功
	 */

	public Po update(CommodityPo commodity){
		String id=commodity.get编号();          //商品编号自动生成
		String commodityTypeName=commodity.get所属分类();
		String commodityTypeID=getCommodityTypeID(commodityTypeName);
		String name=commodity.get名称();
		String model=commodity.get型号();         //商品型号
		long importPrice=commodity.get进价();
		long salePrice=commodity.get售价();
		//long recent_importPrice=commodity.get最近进价(); //由进货单得到,默认为进价
		//long recent_salePrice=commodity.get最近售价();     //由销售单决定，默认为售价
		int dangerNum=commodity.get库存数量警戒值();
	
		Po m=new Po(); 
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.executeUpdate("update "+accountBookPo.getName()+"_commodity set f_commodityTypeID='"+commodityTypeID+"',f_name='"+name+"',f_model='"+model+"',f_importPrice='"+importPrice+"',f_salePrice='"+salePrice+"',f_dangerNum='"+dangerNum+"'where f_commodityID='"+id+"'");
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}
	
	/**
	 * 查找含有相同关键字的商品
	 * @param keyword
	 */
	public List<CommodityPo> find(CommodityPo commodity){
		List<CommodityPo> list=new ArrayList<CommodityPo>();
		String _name=commodity.get名称();
		String _id=commodity.get编号();
		String _model=commodity.get型号();
		String _typeID=commodity.get所属分类();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement(); 
			ResultSet rs=s.executeQuery("select * from "+accountBookPo.getName()+"_commodity where f_commodityID like '%"+_id+"%' or f_name like '%"+_name+"%'"+" or f_model like '%"+_model+"%'"+" or f_commodityTypeID like '%"+_typeID+"%'");
			while(rs.next()){
				CommodityPo cm=new CommodityPo();
				String name=rs.getString("f_name");
				String commodityTypeID=rs.getString("f_commodityTypeID");
				String commodityTypeName=getCommodityTypeName(commodityTypeID);
				String id=rs.getString("f_commodityID");
				String model=rs.getString("f_model");
				int stockNum=rs.getInt("f_stockNum");
				long importPrice=rs.getLong("f_importPrice");
				long salePrice=rs.getLong("f_salePrice");
				long recent_importPrice=rs.getLong("f_recent_importPrice");
				long recent_salePrice=rs.getLong("f_recent_salePrice");
				int dangerNum=rs.getInt("f_dangerNum");
			    cm.set名称(name);
			    cm.set售价(salePrice);
			    cm.set型号(model);
			    cm.set库存数量(stockNum);
			    cm.set库存数量警戒值(dangerNum);
			    cm.set所属分类(commodityTypeName);
			    cm.set最近售价(recent_salePrice);
			    cm.set最近进价(recent_importPrice);
			    cm.set编号(id);
			    cm.set进价(importPrice);
			    list.add(cm);
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		return list;
		
	}
	private String getCommodityTypeName(String commodityTypeID){
		String name="";
		Connection c=Database.getConnection();
		try {
			Statement s=c.createStatement();
			ResultSet rs=s.executeQuery("select f_name from "+accountBookPo.getName()+"_commodityType where f_commodityTypeID='"+commodityTypeID+"'");
			while(rs.next()){
				name=rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return name;
	}
	private String getCommodityTypeID(String commodityTypeName){
		String id="";
		Connection c=Database.getConnection();
		try {
			Statement s=c.createStatement();
			ResultSet rs=s.executeQuery("select f_commodityTypeID from "+accountBookPo.getName()+"_commodityType where f_name='"+commodityTypeName+"'");
			while(rs.next()){
				id=rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return id;
	}
}
